import pymysql
from enum import Enum
from typing import List
import pandas as pd
from config import config_instance
from db_decorator_apply import con_my_sql,document_con_my_sql

document_conn = pymysql.connect(**config_instance.document_conn_config)

class DocumentStatus(Enum):
    DRAFT = '草稿'
    PENDING_REVIEW = '待审核'
    REVIEW_FAILED = '审核失败'
    PENDING_SIGN = '待签收'
    VIEWED = '已查看'
    SIGNED = '已签收'
    SIGN_FAILED = '签收失败'

class SecurityLevel(Enum):
    NONE = '无'
    CONFIDENTIAL = '秘密'
    SECRET = '机密'
    TOP_SECRET = '绝密'

# 定义操作符枚举
class Operator(Enum):
    IN = 'IN'
    NOT_IN = 'NOT IN'
    EQUAL = '='
    NOT_EQUAL = '!='

# insert_documents_code = "INSERT INTO documents (title, content, author_id, author_name, drafting_unit, receiving_unit, status, security_level, sm4_iv) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
# update_documents_code = "UPDATE documents SET title=%s, content=%s, author_id=%s, author_name=%s, status=%s, security_level=%s, sm4_iv=%s WHERE id=%s"
# delete_documents_code = "DELETE FROM documents WHERE id=%s"
# find_document_by_id_code = "SELECT * FROM documents WHERE id=%s"
# find_all_documents_code = "SELECT * FROM documents"

def insert_document(title, content, author_id, author_name, drafting_unit, receiving_unit, status, security_level, sm4_iv=None):
    """
    插入新的公文记录。

    :param title: 公文标题
    :param content: 公文内容
    :param author_id: 作者ID
    :param author_name: 作者用户名
    :param drafting_unit: 发文单位
    :param receiving_unit: 收文单位
    :param status: 状态  使用枚举时记得取出具体的值！
    :param security_level: 密级 使用枚举时记得取出具体的值！
    :param sm4_iv: SM4 IV值（可选）
    """
    param_dict = {
        "title": title,
        "content": content,
        "author_id": author_id,
        "author_name": author_name,
        "drafting_unit": drafting_unit,
        "receiving_unit": receiving_unit,
        "status": status,
        "security_level": security_level,
        "sm4_iv": sm4_iv
    }
    # 构建插入语句
    insert_documents_code = "INSERT INTO documents (title, content, author_id, author_name, drafting_unit, receiving_unit, status, security_level, sm4_iv) VALUES (%(title)s, %(content)s, %(author_id)s, %(author_name)s, %(drafting_unit)s, %(receiving_unit)s, %(status)s, %(security_level)s, %(sm4_iv)s)"
    try:
        document_con_my_sql(insert_documents_code, param_dict, document_conn)
    except Exception as e:
        print("公文数据库处理类错误： ", str(e))
        return []

def update_document(document_id, title, content, author_id, author_name, status, security_level, sm4_iv=None):
    """
    更新公文记录。

    :param document_id: 公文ID
    :param title: 公文标题
    :param content: 公文内容
    :param author_id: 作者ID
    :param author_name: 作者用户名
    :param status: 状态
    :param security_level: 密级
    :param sm4_iv: SM4 IV值（可选）
    """
    param_dict = {
        "title": title,
        "content": content,
        "author_id": author_id,
        "author_name": author_name,
        "status": status,
        "security_level": security_level,
        "sm4_iv": sm4_iv,
        "document_id": document_id
    }
    update_documents_code = "UPDATE documents SET title=%(title)s, content=%(content)s, author_id=%(author_id)s, author_name=%(author_name)s, status=%(status)s, security_level=%(security_level)s, sm4_iv=%(sm4_iv)s WHERE id=%(document_id)s"
    try:
        document_con_my_sql(update_documents_code, param_dict, document_conn)
    except Exception as e:
        print("公文数据库处理类错误： ",str(e))
        return []

def update_document_status(document_id, new_status):
    """
    更新公文的状态。

    :param document_id: 公文ID
    :param new_status: 新的状态
    """
    param_dict = {
        "status": new_status,
        "document_id": document_id
    }
    update_query = "UPDATE documents SET status = %(status)s WHERE id = %(document_id)s"
    try:
        document_con_my_sql(update_query, param_dict, document_conn)
    except Exception as e:
        print("公文数据库处理类错误： ",str(e))
        return []

def update_document_edit_commit(document_id, title, content, receiving_unit=None, security_level=None):
    """
    部分更新公文记录，只更新标题、内容、接受单位、密级、SM4 IV值和状态。

    :param document_id: 公文ID
    :param title: 公文标题
    :param content: 公文内容
    :param receiving_unit: 接受单位（可选）
    :param security_level: 密级（可选）
    :param sm4_iv: SM4 IV值
    """
    param_dict = {}
    set_clauses = []

    if title:
        set_clauses.append("title=%(title)s")
        param_dict["title"] = title
    if content:
        set_clauses.append("content=%(content)s")
        param_dict["content"] = content
    if receiving_unit:
        set_clauses.append("receiving_unit=%(receiving_unit)s")
        param_dict["receiving_unit"] = receiving_unit
    if security_level:
        set_clauses.append("security_level=%(security_level)s")
        param_dict["security_level"] = security_level
    sm4_iv = b'0'
    set_clauses.append("sm4_iv=%(sm4_iv)s")
    param_dict["sm4_iv"] = sm4_iv

    if not set_clauses:
        print("No fields to update.")
        return

    set_clause = ", ".join(set_clauses)
    update_code = f"UPDATE documents SET {set_clause}, modified_date=CURRENT_TIMESTAMP WHERE id=%(document_id)s"
    param_dict["document_id"] = document_id

    try:
        document_con_my_sql(update_code, param_dict, document_conn)
    except Exception as e:
        print("公文数据库处理类错误： ", str(e))
        return []

def delete_document(document_id):
    """
    删除指定ID的公文记录。

    :param document_id: 公文ID
    """
    param_dict = {"document_id": document_id}
    delete_documents_code = "DELETE FROM documents WHERE id=%(document_id)s"
    result = document_con_my_sql(delete_documents_code, param_dict, document_conn)

    if isinstance(result, tuple) and issubclass(result[0], Exception):
        print(f"Error deleting document: {result[1]}")
    else:
        print("Document deleted successfully.")


def get_document_by_id(document_id):
    """
    根据ID查找公文记录。

    :param document_id: 公文ID
    :return: 如果找到记录，返回记录字典；如果未找到或出现异常，返回None
    """
    param_dict = {"document_id": document_id}
    find_document_by_id_code = "SELECT * FROM documents WHERE id=%(document_id)s"
    result = document_con_my_sql(find_document_by_id_code, param_dict, document_conn)
    try:
        if result:
            document = result[0]
            return document
        else:
            print("Document not found.")
            return None
    except Exception as e:
        print("公文数据库处理类错误： ",str(e))
        return []

def get_all_documents():
    """
    获取所有公文记录。

    :return: 如果找到记录，返回记录列表；如果未找到或出现异常，返回空列表
    """
    find_all_documents_code = "SELECT * FROM documents"
    result = document_con_my_sql(find_all_documents_code, None, document_conn)

    try:
        documents = result
        if documents:
            return documents
        else:
            print("No documents found.")
            return []
    except Exception as e:
        print("公文数据库处理类错误： ",str(e))
        return []

# warning: 下面的部分没有使用基于字典的参数传递方法。其主要用于筛查并返回一个列表，不需要“内容”模板的加密。
# warning: 所以下面的con-my-sql语句可能不适用于装饰器。如果要用，请在装饰器编写时额外注意对“元组”类型数据的忽略。

class DocumentQueryCriteria:
    def __init__(self):
        self.criteria = {}

    def add_status(self, operator, values):
        if not isinstance(values, list) or not all(isinstance(value, DocumentStatus) for value in values):
            raise ValueError("Values must be a list of DocumentStatus enums")
        self.criteria['status'] = (operator, values)

    def add_security_level(self, operator, values):
        if not isinstance(values, list) or not all(isinstance(value, SecurityLevel) for value in values):
            raise ValueError("Values must be a list of SecurityLevel enums")
        self.criteria['security_level'] = (operator, values)

    def add_modified_date(self, operator, value):
        if not isinstance(value, str):
            raise ValueError("Value must be a string representing the date")
        self.criteria['modified_date'] = (operator, value)

    def add_author_id(self, operator, value):
        if not isinstance(value, int):
            raise ValueError("Value must be an integer representing the author ID")
        self.criteria['author_id'] = (operator, value)

    def get_criteria(self):
        return self.criteria

def get_documents_by_criteria(criteria):
    """
    根据给定的标准（如状态、密级等）查询公文记录。

    :param criteria: 一个字典，键是数据库字段名，值是一个元组，包含操作符(Operator)和值列表或单个值
    :return: 如果找到记录，返回记录列表；如果未找到或出现异常，返回空列表
    """
    if not criteria:
        print("Criteria is empty. Please provide at least one criterion.")
        return []

    # 构建SQL查询语句
    where_clauses = []
    params = []

    for field, (operator, values) in criteria.items():
        if operator == Operator.IN or operator == Operator.NOT_IN:
            if not values:
                continue  # 跳过空值列表
            # 将枚举成员转换为字符串列表
            if all(isinstance(value, Enum) for value in values):
                values = [value.value for value in values]
            placeholders = ', '.join(['%s'] * len(values))
            where_clauses.append(f"{field} {operator.value} ({placeholders})")
            params.extend(values)
        elif operator == Operator.EQUAL or operator == Operator.NOT_EQUAL:
            # 处理单个值的情况
            if isinstance(values, Enum):
                values = values.value
            where_clauses.append(f"{field} {operator.value} %s")
            params.append(values)
        else:
            raise ValueError(f"Unsupported operator: {operator}")

    where_clause = ' AND '.join(where_clauses)
    query = f"SELECT * FROM documents WHERE {where_clause}"

    result = con_my_sql(document_conn, query, tuple(params))
    try:
        documents = result
        if documents:
            return documents
        else:
            return []
    except Exception as e:
        print("公文数据库处理类错误： ",str(e))
        return []

def get_document_list(author_id: int) -> List[dict]:
    """
    根据给定的作者ID，查询满足特定状态条件的公文记录，并返回两次查询结果的合集。

    具体查询条件为：
    1. 查询作者ID一致且状态是草稿和审核失败的公文。
    2. 查询状态是已发布和已签收的公文。

    :param author_id: 作者的ID
    :return: 满足条件的公文记录列表的合集，如果没有找到匹配的记录则返回空列表
    """
    # 第一次查询：作者ID一致且状态是草稿和审核失败的公文
    # 改为返回作者ID一致的所有公文
    criteria1 = DocumentQueryCriteria()
    criteria1.add_author_id(Operator.EQUAL, author_id)
    # criteria1.add_status(Operator.IN, [DocumentStatus.DRAFT, DocumentStatus.REVIEW_FAILED])
    result1 = get_documents_by_criteria(criteria1.get_criteria())

    # 第二次查询：状态是已发布(待签收的本地就是已发布)和已签收的公文
    criteria2 = DocumentQueryCriteria()
    criteria2.add_status(Operator.IN, [DocumentStatus.SIGNED, DocumentStatus.PENDING_SIGN])
    result2 = get_documents_by_criteria(criteria2.get_criteria())

    # 将两次查询结果合并为一个列表
    combined_result = result1 + result2

    # 使用 pandas 去重
    df = pd.DataFrame(combined_result)
    df.drop_duplicates(inplace=True)
    combined_result = df.to_dict('records')

    return combined_result

# # 使用示例
# criteria = DocumentQueryCriteria()
# criteria.add_status(Operator.IN, [DocumentStatus.DRAFT, DocumentStatus.PENDING_REVIEW])
# criteria.add_security_level(Operator.NOT_IN, [SecurityLevel.CONFIDENTIAL])
# criteria.add_modified_date(Operator.NOT_EQUAL, '2024-12-01')
#
# # 获取最终的查询条件
# query_criteria = criteria.get_criteria()
#
# # 执行查询
# documents = find_documents_by_criteria(query_criteria)
# for doc in documents:
#     print(doc)

# # 示例调用
# if __name__ == "__main__":
#     doc_manager = DBDocumentManager()
#
#     # 插入新公文
#     doc_manager.insert_document(
#         title="XX新公文",
#         content="这是新公文的内容。",
#         author_id=123,
#         author_name="张三",
#         status="draft",
#         security_level="secret",
#         sm4_iv=b'\x00' * 16  # 假设这是一个16字节的IV值
#     )
#
#     # 更新公文
#     doc_manager.update_document(
#         document_id=7,
#         title="更新后的公文标题",
#         content="这是更新后的公文内容。",
#         author_id=123,
#         author_name="李四",
#         status="published",
#         security_level="confidential",
#         sm4_iv=b'\x01' * 16  # 假设这是一个16字节的IV值
#     )
#
#     # 查找公文 by id
#     document = doc_manager.find_document_by_id(2)
#     if document:
#         print(document)
#
#     # 查找公文 by title
#     documents = doc_manager.find_documents_by_title("新公文")
#     if documents:
#         for doc in documents:
#             print(doc)
#
#     # 删除公文
#     doc_manager.delete_document(9)

# # 示例调用
# statuses_to_search = [DocumentStatus.DRAFT]
# documents = find_documents_by_status(statuses_to_search)
# for doc in documents:
#     print(doc)

# # 根据密级查询公文函数示例
# security_levels_to_search = [SecurityLevel.NONE, SecurityLevel.TOP_SECRET]
# documents = find_documents_by_security_level(security_levels_to_search)
# for doc in documents:
#     print(doc)